import sqlite3
from datetime import datetime
import os
import hashlib
import uuid
import random

# 确保数据目录存在
os.makedirs('data', exist_ok=True)
DB_PATH = 'data/water_meter.db'


def init_db():
    """初始化数据库"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # 用户表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        salt TEXT NOT NULL,
        role TEXT NOT NULL,
        create_time DATETIME
    )
    ''')

    # 水表读数表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS meter_readings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        meter_code TEXT,
        reading_value TEXT,
        image_path TEXT,
        user_id INTEGER,
        timestamp DATETIME,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    ''')

    # 水费账单表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS bills (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        meter_code TEXT NOT NULL,
        start_reading TEXT,
        end_reading TEXT,
        water_usage REAL,
        amount REAL,
        status TEXT DEFAULT 'pending_review',
        bill_date DATETIME,
        payment_date DATETIME,
        payment_method TEXT,
        review_date DATETIME,
        reviewer_id INTEGER,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (reviewer_id) REFERENCES users(id)
    )
    ''')

    # 费率表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS water_rates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        rate_type TEXT NOT NULL,
        min_usage REAL,
        max_usage REAL,
        unit_price REAL NOT NULL,
        effective_date DATETIME
    )
    ''')

    # 异常记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS anomalies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        meter_code TEXT,
        reading_id INTEGER,
        anomaly_type TEXT NOT NULL,
        description TEXT,
        status TEXT DEFAULT 'unresolved',
        report_date DATETIME,
        resolved_date DATETIME,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (reading_id) REFERENCES meter_readings(id)
    )
    ''')

    # 水务规划表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS water_plans (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        plan_title TEXT NOT NULL,
        description TEXT,
        area TEXT,
        population INTEGER,
        estimated_usage REAL,
        plan_date DATETIME,
        implementation_date DATETIME,
        status TEXT DEFAULT 'draft'
    )
    ''')

    # 审计日志表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS audit_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        username TEXT,
        action TEXT NOT NULL,
        action_time DATETIME,
        ip_address TEXT,
        status TEXT,
        details TEXT,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    ''')

    # 检查表结构，确保有user_id列
    cursor.execute("PRAGMA table_info(meter_readings)")
    columns = [column[1] for column in cursor.fetchall()]
    if 'user_id' not in columns:
        # 添加user_id列
        cursor.execute("ALTER TABLE meter_readings ADD COLUMN user_id INTEGER REFERENCES users(id)")

    # 检查是否存在管理员用户，如果不存在则创建默认管理员
    cursor.execute("SELECT id FROM users WHERE username = 'admin'")
    if not cursor.fetchone():
        # 创建默认管理员账户 (用户名: admin, 密码: admin123)
        salt = uuid.uuid4().hex
        password_hash = hashlib.sha256(('admin123' + salt).encode()).hexdigest()
        cursor.execute(
            "INSERT INTO users (username, password_hash, salt, role, create_time) VALUES (?, ?, ?, ?, ?)",
            ('admin', password_hash, salt, 'admin', datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )

    # 检查是否存在水费率数据，如果不存在则添加默认费率
    cursor.execute("SELECT id FROM water_rates LIMIT 1")
    if not cursor.fetchone():
        # 添加默认水费率
        cursor.execute(
            "INSERT INTO water_rates (rate_type, min_usage, max_usage, unit_price, effective_date) VALUES (?, ?, ?, ?, ?)",
            ('residential_basic', 0, 10, 3.0, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )
        cursor.execute(
            "INSERT INTO water_rates (rate_type, min_usage, max_usage, unit_price, effective_date) VALUES (?, ?, ?, ?, ?)",
            ('residential_medium', 10, 30, 3.5, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )
        cursor.execute(
            "INSERT INTO water_rates (rate_type, min_usage, max_usage, unit_price, effective_date) VALUES (?, ?, ?, ?, ?)",
            ('residential_high', 30, None, 4.0, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )

    conn.commit()
    conn.close()


def hash_password(password, salt=None):
    """对密码进行哈希处理"""
    if not salt:
        salt = uuid.uuid4().hex

    password_hash = hashlib.sha256((password + salt).encode()).hexdigest()
    return password_hash, salt


def add_meter_reading(meter_code, reading_value, image_path, user_id=None):
    """添加水表读数记录"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO meter_readings (meter_code, reading_value, image_path, user_id, timestamp) VALUES (?, ?, ?, ?, ?)",
        (meter_code, reading_value, image_path, user_id, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    )

    # 获取添加的记录ID
    reading_id = cursor.lastrowid

    # 自动检测异常情况
    check_for_anomalies(cursor, reading_id, meter_code, reading_value, user_id)

    # 自动生成待审核账单
    generate_bill_if_needed(cursor, meter_code, reading_value, user_id, 'pending_review')

    conn.commit()
    conn.close()

    return reading_id


def check_for_anomalies(cursor, reading_id, meter_code, reading_value, user_id):
    """检查是否有异常读数"""
    try:
        # 获取上一次读数
        cursor.execute("""
            SELECT reading_value FROM meter_readings 
            WHERE meter_code = ? AND user_id = ? AND id != ? 
            ORDER BY timestamp DESC LIMIT 1
        """, (meter_code, user_id, reading_id))

        prev_reading = cursor.fetchone()

        if prev_reading:
            try:
                current = float(reading_value)
                previous = float(prev_reading[0])

                # 检查异常情况：读数减少
                if current < previous:
                    add_anomaly(cursor, user_id, meter_code, reading_id, "reading_decreased",
                                f"读数减少：从 {previous} 到 {current}")

                # 检查异常情况：用水量异常
                usage = current - previous
                if usage > 100:  # 假设超过100立方米是异常
                    add_anomaly(cursor, user_id, meter_code, reading_id, "excessive_usage",
                                f"用水量异常：{usage} 立方米")
            except ValueError:
                # 如果读数无法转换为数字，记录异常
                add_anomaly(cursor, user_id, meter_code, reading_id, "invalid_reading",
                            "读数无法解析为数字")
    except Exception as e:
        print(f"检查异常时出错: {str(e)}")


def add_anomaly(cursor, user_id, meter_code, reading_id, anomaly_type, description):
    """添加异常记录"""
    cursor.execute("""
        INSERT INTO anomalies (user_id, meter_code, reading_id, anomaly_type, description, report_date, status)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (user_id, meter_code, reading_id, anomaly_type, description,
          datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 'unresolved'))


def generate_bill_if_needed(cursor, meter_code, reading_value, user_id, status):
    """根据读数生成账单"""
    try:
        # 检查是否存在未付账单
        cursor.execute("""
            SELECT id FROM bills 
            WHERE meter_code = ? AND user_id = ? AND status = ?
            LIMIT 1
        """, (meter_code, user_id, status))

        if cursor.fetchone():
            # 已有未付账单，不再生成
            return

        # 获取上一次读数
        cursor.execute("""
            SELECT reading_value FROM meter_readings 
            WHERE meter_code = ? AND user_id = ? 
            ORDER BY timestamp DESC LIMIT 2
        """, (meter_code, user_id))

        readings = cursor.fetchall()

        if len(readings) >= 2:
            try:
                end_reading = float(readings[0][0])  # 最新读数
                start_reading = float(readings[1][0])  # 上一次读数

                water_usage = end_reading - start_reading
                if water_usage > 0:
                    # 计算费用
                    amount = calculate_water_bill(cursor, water_usage)

                    # 创建账单
                    cursor.execute("""
                        INSERT INTO bills (user_id, meter_code, start_reading, end_reading, 
                                         water_usage, amount, status, bill_date)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    """, (user_id, meter_code, str(start_reading), str(end_reading), water_usage, amount,
                          status, datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
            except ValueError:
                # 无法转换为数字，忽略账单生成
                pass
    except Exception as e:
        print(f"生成账单时出错: {str(e)}")


def calculate_water_bill(cursor, water_usage):
    """计算水费"""
    cursor.execute("""
        SELECT rate_type, min_usage, max_usage, unit_price 
        FROM water_rates 
        ORDER BY min_usage ASC
    """)

    rates = cursor.fetchall()
    total_amount = 0
    remaining_usage = water_usage

    for rate in rates:
        rate_type, min_usage, max_usage, unit_price = rate
        min_usage = float(min_usage)
        max_usage = float(max_usage) if max_usage is not None else float('inf')
        unit_price = float(unit_price)

        if remaining_usage <= 0:
            break

        tier_usage = min(remaining_usage, max_usage - min_usage)
        tier_amount = tier_usage * unit_price
        total_amount += tier_amount
        remaining_usage -= tier_usage

    return round(total_amount, 2)


def get_all_readings(user_id=None):
    """获取所有水表读数记录"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    try:
        if user_id and user_id > 0:
            cursor.execute("SELECT * FROM meter_readings WHERE user_id = ? ORDER BY timestamp DESC", (user_id,))
        else:
            cursor.execute("SELECT * FROM meter_readings ORDER BY timestamp DESC")

        readings = [dict(row) for row in cursor.fetchall()]
    except sqlite3.OperationalError:
        # 如果查询出错，可能是表结构问题，尝试修复表
        conn.close()
        init_db()  # 重新初始化数据库
        # 再次连接并尝试查询
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM meter_readings ORDER BY timestamp DESC")
        readings = [dict(row) for row in cursor.fetchall()]

    conn.close()
    return readings


def get_reading_by_id(reading_id):
    """根据ID获取特定的水表读数记录"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM meter_readings WHERE id = ?", (reading_id,))
    reading = cursor.fetchone()
    conn.close()
    return dict(reading) if reading else None


def register_user(username, password, role='user'):
    """注册新用户"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # 检查用户名是否已存在
    cursor.execute("SELECT id FROM users WHERE username = ?", (username,))
    if cursor.fetchone():
        conn.close()
        return False, "用户名已存在"

    # 创建新用户
    password_hash, salt = hash_password(password)
    try:
        cursor.execute(
            "INSERT INTO users (username, password_hash, salt, role, create_time) VALUES (?, ?, ?, ?, ?)",
            (username, password_hash, salt, role, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )
        conn.commit()
        conn.close()
        return True, "注册成功"
    except Exception as e:
        conn.close()
        return False, f"注册失败: {str(e)}"


def authenticate_user(username, password):
    """验证用户登录"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    # 查找用户
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    user = cursor.fetchone()

    if not user:
        # 记录失败登录
        add_audit_log(None, username, "登录", "失败", "用户不存在")
        conn.close()
        return None, "用户不存在"

    # 验证密码
    password_hash = hashlib.sha256((password + user['salt']).encode()).hexdigest()
    if password_hash != user['password_hash']:
        # 记录失败登录
        add_audit_log(user['id'], username, "登录", "失败", "密码错误")
        conn.close()
        return None, "密码错误"

    # 记录成功登录
    add_audit_log(user['id'], username, "登录", "成功")

    conn.close()
    return dict(user), "登录成功"


def get_user_by_id(user_id):
    """根据ID获取用户信息"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT id, username, role, create_time FROM users WHERE id = ?", (user_id,))
    user = cursor.fetchone()
    conn.close()
    return dict(user) if user else None


def get_all_users():
    """获取所有用户信息（仅管理员可用）"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT id, username, role, create_time FROM users")
    users = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return users


def admin_update_user(user_id, username=None, role=None, reset_password=False):
    """管理员更新用户信息（仅管理员可用）"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        # 检查用户是否存在
        cursor.execute("SELECT id, username FROM users WHERE id = ?", (user_id,))
        user = cursor.fetchone()
        if not user:
            conn.close()
            return False, "用户不存在"

        # 修改用户名
        if username and username != user[1]:
            # 检查用户名是否已存在
            cursor.execute("SELECT id FROM users WHERE username = ? AND id != ?", (username, user_id))
            if cursor.fetchone():
                conn.close()
                return False, "用户名已存在"

            # 更新用户名
            cursor.execute("UPDATE users SET username = ? WHERE id = ?", (username, user_id))

        # 修改角色
        if role:
            cursor.execute("UPDATE users SET role = ? WHERE id = ?", (role, user_id))

        # 重置密码
        if reset_password:
            # 生成随机密码
            new_password = ''.join(
                random.choices('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', k=8))

            # 盐值和哈希
            salt = uuid.uuid4().hex
            password_hash = hashlib.sha256((new_password + salt).encode()).hexdigest()

            # 更新密码
            cursor.execute("UPDATE users SET password_hash = ?, salt = ? WHERE id = ?",
                           (password_hash, salt, user_id))

            conn.commit()
            conn.close()
            return True, f"用户信息已更新，新密码为：{new_password}"

        conn.commit()
        conn.close()
        return True, "用户信息已更新"
    except Exception as e:
        conn.close()
        return False, f"更新失败: {str(e)}"


def delete_user(user_id):
    """删除用户（仅管理员可用）"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        # 检查是否为最后一个管理员
        cursor.execute("SELECT role FROM users WHERE id = ?", (user_id,))
        user_role = cursor.fetchone()

        if user_role and user_role[0] == 'admin':
            cursor.execute("SELECT COUNT(*) FROM users WHERE role = 'admin'")
            admin_count = cursor.fetchone()[0]

            if admin_count <= 1:
                conn.close()
                return False, "无法删除最后一个管理员账户"

        # 删除用户相关的记录
        cursor.execute("DELETE FROM meter_readings WHERE user_id = ?", (user_id,))
        cursor.execute("DELETE FROM bills WHERE user_id = ?", (user_id,))
        cursor.execute("DELETE FROM anomalies WHERE user_id = ?", (user_id,))

        # 删除用户
        cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))

        if cursor.rowcount == 0:
            conn.close()
            return False, "用户不存在"

        conn.commit()
        conn.close()
        return True, "用户已删除"
    except Exception as e:
        conn.close()
        return False, f"删除失败: {str(e)}"


def update_user_info(user_id, username=None, old_password=None, new_password=None):
    """更新用户信息"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        # 如果要修改用户名，检查是否存在重名
        if username:
            cursor.execute("SELECT id FROM users WHERE username = ? AND id != ?", (username, user_id))
            if cursor.fetchone():
                conn.close()
                return False, "用户名已存在"

            cursor.execute("UPDATE users SET username = ? WHERE id = ?", (username, user_id))

        # 如果要修改密码，验证旧密码
        if old_password and new_password:
            cursor.execute("SELECT password_hash, salt FROM users WHERE id = ?", (user_id,))
            user_data = cursor.fetchone()

            if not user_data:
                conn.close()
                return False, "用户不存在"

            old_password_hash = hashlib.sha256((old_password + user_data[1]).encode()).hexdigest()

            # 验证旧密码
            if old_password_hash != user_data[0]:
                conn.close()
                return False, "旧密码不正确"

            # 生成新密码哈希
            new_password_hash, salt = hash_password(new_password, user_data[1])

            # 更新密码
            cursor.execute("UPDATE users SET password_hash = ? WHERE id = ?", (new_password_hash, user_id))

        conn.commit()
        conn.close()
        return True, "个人信息已更新"
    except Exception as e:
        conn.close()
        return False, f"更新失败: {str(e)}"

    
def get_user_bills(user_id=None, status=None, include_pending=False):
    """获取用户账单

    参数:
        user_id: 用户ID，为None则返回所有用户的账单
        status: 账单状态筛选
        include_pending: 是否包含待审核的账单（仅管理员）
    """
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = "SELECT * FROM bills"
    params = []

    if user_id:
        query += " WHERE user_id = ?"
        params.append(user_id)

        if status:
            query += " AND status = ?"
            params.append(status)
        # 移除对普通用户的状态限制
        # elif not include_pending:
        #     print("普通用户")
        #     # 普通用户默认只能看到已审核的账单（approved或unpaid或paid）
        #     query += " AND status != 'pending_review'"
    elif status:
        query += " WHERE status = ?"
        params.append(status)
    elif not include_pending:
        # 默认不包含待审核账单
        print("默认不包含待审核账单")
        query += " WHERE status != 'pending_review'"

    query += " ORDER BY bill_date DESC"

    # Print the query and parameters for debugging
    print(f"Executing query: {query}")
    print(f"With parameters: {params}")

    cursor.execute(query, params)
    bills = [dict(row) for row in cursor.fetchall()]

    # Print the number of bills fetched
    print(f"Fetched {len(bills)} bills")

    conn.close()
    return bills


def pay_bill(bill_id, payment_method):
    """支付账单"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        cursor.execute("""
            UPDATE bills 
            SET status = 'paid', payment_date = ?, payment_method = ?
            WHERE id = ? AND status = 'unpaid'
        """, (datetime.now().strftime('%Y-%m-%d %H:%M:%S'), payment_method, bill_id))

        if cursor.rowcount > 0:
            conn.commit()
            conn.close()
            return True, "支付成功"
        else:
            conn.close()
            return False, "账单不存在或已支付"
    except Exception as e:
        conn.close()
        return False, f"支付失败: {str(e)}"


def get_anomalies(user_id=None, status=None):
    """获取异常记录"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = "SELECT * FROM anomalies"
    params = []

    if user_id:
        query += " WHERE user_id = ?"
        params.append(user_id)

        if status:
            query += " AND status = ?"
            params.append(status)
    elif status:
        query += " WHERE status = ?"
        params.append(status)

    query += " ORDER BY report_date DESC"

    cursor.execute(query, params)
    anomalies = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return anomalies


def resolve_anomaly(anomaly_id, resolution_note):
    """解决异常"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        cursor.execute("""
            UPDATE anomalies 
            SET status = 'resolved', resolved_date = ?, description = description || ' | 解决方案: ' || ?
            WHERE id = ? AND status = 'unresolved'
        """, (datetime.now().strftime('%Y-%m-%d %H:%M:%S'), resolution_note, anomaly_id))

        if cursor.rowcount > 0:
            conn.commit()
            conn.close()
            return True, "问题已解决"
        else:
            conn.close()
            return False, "异常记录不存在或已解决"
    except Exception as e:
        conn.close()
        return False, f"操作失败: {str(e)}"


def add_water_plan(plan_title, description, area, population, estimated_usage, implementation_date, status='draft'):
    """添加水务规划"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        cursor.execute("""
            INSERT INTO water_plans (plan_title, description, area, population, estimated_usage, 
                                  plan_date, implementation_date, status)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (plan_title, description, area, population, estimated_usage,
              datetime.now().strftime('%Y-%m-%d %H:%M:%S'), implementation_date, status))

        conn.commit()
        conn.close()
        return True, "水务规划已添加"
    except Exception as e:
        conn.close()
        return False, f"添加失败: {str(e)}"


def get_water_plans(status=None):
    """获取水务规划"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    if status:
        cursor.execute("SELECT * FROM water_plans WHERE status = ? ORDER BY plan_date DESC", (status,))
    else:
        cursor.execute("SELECT * FROM water_plans ORDER BY plan_date DESC")

    plans = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return plans


def update_water_plan(plan_id, plan_title, description, area, population, estimated_usage, implementation_date, status):
    """更新水务规划"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        # 检查该ID的规划是否存在
        cursor.execute("SELECT id FROM water_plans WHERE id = ?", (plan_id,))
        if not cursor.fetchone():
            conn.close()
            return False, "找不到指定ID的规划"

        cursor.execute("""
            UPDATE water_plans 
            SET plan_title = ?, description = ?, area = ?, population = ?, 
                estimated_usage = ?, implementation_date = ?, status = ?
            WHERE id = ?
        """, (plan_title, description, area, population, estimated_usage,
              implementation_date, status, plan_id))

        conn.commit()
        conn.close()
        return True, "水务规划已更新"
    except Exception as e:
        conn.close()
        return False, f"更新失败: {str(e)}"


def add_audit_log(user_id, username, action, status, details=None, ip_address=None):
    """添加审计日志"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        cursor.execute("""
            INSERT INTO audit_logs (user_id, username, action, action_time, ip_address, status, details)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (user_id, username, action, datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
              ip_address, status, details))

        conn.commit()
    except Exception as e:
        print(f"审计日志记录失败: {str(e)}")
    finally:
        conn.close()


def get_audit_logs(limit=100):
    """获取审计日志"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    cursor.execute("""
        SELECT * FROM audit_logs 
        ORDER BY action_time DESC
        LIMIT ?
    """, (limit,))

    logs = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return logs


def review_bill(bill_id, reviewer_id, approve=True):
    """审核账单

    参数:
        bill_id: 账单ID
        reviewer_id: 审核人ID
        approve: 是否批准，True为批准，False为拒绝
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        # 获取账单当前状态
        cursor.execute("SELECT status FROM bills WHERE id = ?", (bill_id,))
        result = cursor.fetchone()

        if not result:
            conn.close()
            return False, "账单不存在"

        if result[0] != 'pending_review':
            conn.close()
            return False, "账单已审核，不能重复审核"

        status = 'unpaid' if approve else 'rejected'

        cursor.execute("""
            UPDATE bills 
            SET status = ?, reviewer_id = ?, review_date = ?
            WHERE id = ? AND status = 'pending_review'
        """, (status, reviewer_id, datetime.now().strftime('%Y-%m-%d %H:%M:%S'), bill_id))

        if cursor.rowcount > 0:
            conn.commit()
            conn.close()
            return True, "账单已" + ("批准" if approve else "拒绝")
        else:
            conn.close()
            return False, "账单状态已变更，无法审核"
    except Exception as e:
        conn.close()
        return False, f"审核失败: {str(e)}" 